﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CinemaBookingProject.DBLayer;
using System.Threading;
using System.Configuration;

public class DBTicket
{
    static internal SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Balder"].ConnectionString);//new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dmaa0912_E8;Persist Security Info=True;User ID=dmaa0912_E8;Password=MaaGodt");
    private static object obj = new object();

    DBShowTime dbst = new DBShowTime();
    DBSeat dbseat = new DBSeat();
    //DBBooking dbb = new DBBooking();

    public List<Ticket> Read(Ticket tick)
    {


        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket WHERE SeatID = @Seat AND ShowTimeID = @ShowTime AND Invalid = @invalid", Connection);
            cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = tick.Seat.CinemaRoom * 10000 + tick.Seat.Row * 100 + tick.Seat.SeatNr;
            cmd.Parameters.Add("@ShowTime", SqlDbType.Int).Value = dbst.GetID(tick.ShowTime);
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]),tick.Booking));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }
    public List<Ticket> ReadAll()
    {
        

        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket Where Invalid = @invalid", Connection);
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]),null));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public List<Ticket> Read(Booking booking)
    {
        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket INNER JOIN Booking ON Ticket.BookingID=Booking.ID WHERE CustomerPhone = @Phone AND Invalid = @invalid", Connection);
            cmd.Parameters.Add("@Phone", SqlDbType.VarChar, 255).Value = booking.CustomerPhone;
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), booking));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }
    /// <summary>
    /// Returns a list of tickets based on a showtime.
    /// </summary>
    /// <param name="sht">ShowTime</param>
    /// <returns>List of Tickets</returns>
    public List<Ticket> Read(ShowTime sht)
    {
        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket INNER JOIN ShowTime ON Ticket.ShowTimeID=ShowTime.ID WHERE ShDate=@Date AND ShTime = @Time AND CinemaNo = @Room AND Invalid = @invalid", Connection);
            cmd.Parameters.Add("@Date", SqlDbType.Date).Value = sht.DateTime.Date.ToShortDateString();
            cmd.Parameters.Add("@Time", SqlDbType.Time).Value = sht.DateTime.ToShortTimeString();
            cmd.Parameters.Add("@Room", SqlDbType.Int).Value = sht.CinemaRoom.Id;
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;


            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), null));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public List<Ticket> ReadAll(int id)
    {


        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket WHERE Id = @ID AND Invalid = @invalid", Connection);
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), null));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public List<Ticket> ReadFromBookingId(int id)
    {
        SqlDataReader rdr = null;
        List<Ticket> returnList = new List<Ticket>();
        DBBooking dbb = new DBBooking();
        try
        {
            Connection.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Ticket INNER JOIN Booking ON Ticket.BookingID=Booking.ID WHERE Booking.ID = @Id AND Invalid = @invalid", Connection);
            cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = 0;

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                returnList.Add(new Ticket((double)rdr["TicketPrice"], dbst.Read((int)rdr["ShowTimeID"]), dbseat.Read((int)rdr["SeatID"]), dbb.Read(id)));
            }
        }
        //Never not close the connection!
        finally
        {
            if (rdr != null)
                rdr.Close();
            if (Connection != null)
                Connection.Close();
        }

        return returnList;
    }

    public int Insert(Ticket tick)
    {
        
        int wentWell = 0;
        string query =
            "INSERT INTO Ticket (TicketPrice, SeatID, ShowTimeID, BookingID) VALUES (@Price, @Seat, @ShowTime, @Booking)";
        //Don't insert something that already exists!
        if (this.Read(tick).Count < 1)
        {
            try
            {
                Monitor.Enter(obj);
                SqlCommand cmd = new SqlCommand(query, Connection);
                cmd.Parameters.Add("@Price", SqlDbType.Float).Value = tick.TicketPrice;
                cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = tick.Seat.CinemaRoom * 10000 + tick.Seat.Row * 100 + tick.Seat.SeatNr;
                cmd.Parameters.Add("@ShowTime", SqlDbType.Int).Value = dbst.GetID(tick.ShowTime);
                cmd.Parameters.Add("@Booking", SqlDbType.Int).Value = tick.Booking.Id;

                Connection.Open();
                wentWell = cmd.ExecuteNonQuery();

            }
            catch (SqlException)
            {
                wentWell = -1;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                Connection.Close();
                Monitor.Exit(obj);
            }
        }
        return wentWell;
    }

    public int Update(Ticket tick)
    {
        int succeed = 0;

        string query = "UPDATE Ticket SET Invalid = @invalid WHERE SeatID = @Seat AND ShowTimeID = @ShowTime";
        try
        {
            Monitor.Enter(obj);

            SqlCommand cmd = new SqlCommand(query, Connection);
            cmd.Parameters.Add("@Seat", SqlDbType.Int).Value = tick.Seat.CinemaRoom * 10000 + tick.Seat.Row * 100 + tick.Seat.SeatNr;
            cmd.Parameters.Add("@ShowTime", SqlDbType.Int).Value = dbst.GetID(tick.ShowTime);
            cmd.Parameters.Add("@invalid", SqlDbType.Bit).Value = tick.Invalid;

            Connection.Open();
            succeed = cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            succeed = -1;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            Connection.Close();
            Monitor.Exit(obj);
        }
        return succeed;
    }

}

